/*1*/
CREATE DATABASE student
    ON PRIMARY
    (
        NAME = 'stu',
        FILENAME = '/D/mydocument/db/mssql/stu_data.mdf',
        SIZE = 5 MB,
        MAXSIZE = 500 MB,
        FILEGROWTH = 10%
        )
    LOG ON
    (
        NAME = 'stu_log',
        FILENAME = '/D/mydocument/db/mssql/stu_log.ldf',
        SIZE = 3 MB,
        FILEGROWTH = 1 MB
        )

CREATE DATABASE SPJ
    ON PRIMARY
    (
        NAME = 'SPJ_data',
        FILENAME = '/D/mydocument/db/mssql/SPJ.mdf',
        SIZE = 10 MB,
        FILEGROWTH = 10%
        )
    LOG ON
    (
        NAME = 'SPJ_log',
        FILENAME = '/D/mydocument/db/mssql/SPJ.ldf',
        SIZE = 1 MB,
        MAXSIZE = 50 MB,
        FILEGROWTH = 1 MB
        )
/*2*/
USE student
GO
CREATE TABLE Student
(
    Sno   CHAR(9) NOT NULL PRIMARY KEY,
    Sname CHAR(20),
    Ssex  CHAR(2),
    Sage  SMALLINT,
    Sdept CHAR(20),
);

CREATE TABLE Course
(
    Cno     CHAR(4)  NOT NULL PRIMARY KEY,
    Cname   CHAR(40) NOT NULL,
    Cpno    CHAR(4),
    CCredit SMALLINT,
    FOREIGN KEY (Cpno) REFERENCES Course (cno),
);

CREATE TABLE SC
(
    Sno   CHAR(9) NOT NULL,
    Cno   CHAR(4) NOT NULL,
    Grade smallint,
    PRIMARY KEY (Sno, Cno),
    FOREIGN KEY (Sno) REFERENCES Student (Sno),
);
/*3*/
INSERT INTO Student(Sno, Sname, Ssex, Sage, Sdept)
VALUES
    (200515001, '赵菁菁', '女', 23, 'CS'),
    (200515002, '李勇', '男', 20, 'CS'),
    (200515003, '张力', '男', 19, 'CS'),
    (200515004, '张衡', '男', 18, 'IS'),
    (200515005, '张向东', '男', 20, 'IS'),
    (200515006, '张向丽', '女', 20, 'IS'),
    (200515007, '王芳', '女', 20, 'CS'),
    (200515008, '王民生', '男', 25, 'MA'),
    (200515009, '王小民', '女', 18, 'MA'),
    (200515010, '李辰', '女', 22, 'MA'),
    (200515011, '张毅', '男', 20, 'WM'),
    (200515012, '杨磊', '女', 20, 'EN'),
    (200515013, '李晨', '女', 19, 'MA'),
    (200515014, '张丰毅', '男', 22, 'CS'),
    (200515015, '李蕾', '女', 21, 'EN'),
    (200515016, '刘社', '男', 21, 'CM'),
    (200515017, '刘星耀', '男', 18, 'CM'),
    (200515018, '李贵', '男', 19, 'EN'),
    (200515019, '林自许', '男', 20, 'WM'),
    (200515020, '马翔', '男', 21, NULL),
    (200515021, '刘峰', '男', 25, 'CS'),
    (200515022, '牛站强', '男', 22, NULL),
    (200515023, '李婷婷', '女', 18, NULL),
    (200515024, '严丽', '女', 20, NULL),
    (200515025, '朱小鸥', '女', 30, 'WM');


INSERT INTO Course(cno, cname, cpno, ccredit)
VALUES
    (1, '数据库', 5, 4),
    (2, '数学', NULL, 2),
    (3, '信息系统', 1, 4),
    (4, '操作系统', 6, 3),
    (5, '数据结构', 7, 4),
    (6, '数据处理', NULL, 2),
    (7, 'PASCAL语言', 6, 4),
    (8, '大学英语', NULL, 4),
    (9, '计算机网络', NULL, 4),
    (10, '人工智能', NULL, 2);

INSERT INTO SC(sno, cno, grade)
VALUES
    ('200515001', '1', 75),
    ('200515002', '1', 85),
    ('200515002', '3', 53),
    ('200515003', '1', 86),
    ('200515004', '1', 74),
    ('200515005', '1', 58),
    ('200515006', '1', 84),
    ('200515004', '2', 46),
    ('200515005', '2', 89),
    ('200515006', '2', 65),
    ('200515008', '2', 72),
    ('200515009', '2', 76),
    ('200515010', '2', 96),
    ('200515010', '8', 86),
    ('200515011', '8', 62),
    ('200515015', '8', 0),
    ('200515018', '8', 58),
    ('200515001', '4', 62),
    ('200515002', '4', 85),
    ('200515021', '9', 54),
    ('200515001', '5', 58),
    ('200515021', '6', 58),
    ('200515001', '7', 70),
    ('200515005', '10', 65),
    ('200515016', '8', NULL),
    ('200515017', '8', NULL);


/*3*/

USE SPJ
GO
CREATE TABLE S
(
    SNO      CHAR(4) NOT NULL PRIMARY KEY,
    SNAME    CHAR(20),
    "STATUS" CHAR(5),
    CITY     NCHAR(10),

);

CREATE TABLE P
(
    PNO    CHAR(4) NOT NULL PRIMARY KEY,
    PNAME  nchar(10),
    COLOR  nchar(3),
    WEIGHT smallint,
);

CREATE TABLE J
(
    JNO   char(4) NOT NULL PRIMARY KEY,
    JNAME nchar(10),
    CITY  nchar(10),
);

CREATE TABLE SPJ
(
    SNO CHAR(4) NOT NULL,
    PNO CHAR(4) NOT NULL,
    JNO char(4) NOT NULL,
    QTY smallint,
    PRIMARY KEY (SNO, PNO, JNO),
    FOREIGN KEY (SNO) REFERENCES S (SNO),
    FOREIGN KEY (PNO) REFERENCES P (PNO),
    FOREIGN KEY (JNO) REFERENCES J (JNO),
);
/*4*/
USE student
CREATE LOGIN teacher WITH PASSWORD ='Teacher1',DEFAULT_DATABASE = student;
CREATE USER teacher FOR LOGIN teacher;
GO
CREATE SCHEMA teacher AUTHORIZATION teacher;

/*5*/
CREATE TABLE "teacher".tea
(
    tno     char(9)   NOT NULL PRIMARY KEY,
    tname   nchar(20) NOT NULL,
    tsd     char(20)  NOT NULL,
    tphone  char(11)  NOT NULL,
    te_mail CHAR(128) NOT NULL,
);

/*6*/
USE student
GO
ALTER TABLE student
    ADD phone CHAR(11);
ALTER TABLE student
    ADD e_mail CHAR(128);

/*7*/
USE student
GO
ALTER TABLE student
    ALTER COLUMN Sdept VARCHAR(20);

/*8*/
USE student
GO
ALTER TABLE "teacher".tea
    DROP COLUMN tphone;
